package net.messagenet.mazor.infra.excel;


import java.io.File;
import java.io.IOException;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;
import java.util.Locale;
import java.util.Properties;

import jxl.Cell;
import jxl.CellView;
import jxl.DateCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.read.biff.BiffException;
import jxl.write.DateFormat;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
import net.messagenet.mazor.DO.DpaDO;
import net.messagenet.mazor.DO.DpaProductDO;

public class DpaRequestExcelHandler {
private Properties excelConstats;
	
	


private WritableCellFormat labelCell;
private WritableCellFormat dataCell;
private WritableCellFormat dateCell;

private WritableSheet excelSheet;

/**
 * c'tor
 * 
 * @param excelConstats properties containing the constants for the excel sheet
 */
public DpaRequestExcelHandler(Properties excelConstats) {
	this.excelConstats = excelConstats;
}

/**
 * This method writes a dpa request object to excel according to the formmat
 * 
 * @param dpa The dpa object which data will be written to the excel
 * @param targetFile the full path of the file which the data wll be written to
 * @throws IOException
 * @throws WriteException
 */
public void writeDPAExcelFile(DpaDO dpa,String targetFile) throws IOException, WriteException{
	File excelFile = new File(targetFile);
	WorkbookSettings wbSettings = new WorkbookSettings();

	wbSettings.setLocale(new Locale("en", "EN"));

	WritableWorkbook workbook = Workbook.createWorkbook(excelFile, wbSettings);
	workbook.createSheet(excelConstats.getProperty("dpa_sheet_name"), 0);
	excelSheet = workbook.getSheet(0);
	createWritersAndView();
	createLabel();
	createContent(dpa);
	createProductData(dpa.getDpaProducts());

	workbook.write();
	workbook.close();
	
}
/**
 * This method builds the products list in the excel sheet
 * 
 * @param dpaProducts
 * @param excelSheet2
 * @throws WriteException 
 * @throws RowsExceededException 
 */
private void createProductData(List<DpaProductDO> dpaProducts) throws RowsExceededException, WriteException {
	int row=9;
	
	for (DpaProductDO dpaProductDO : dpaProducts) {
		writeProductLine(row,dpaProductDO);			
		row++;
	}
	
	
}

/**
 * This method creates a line of a Product
 * 
 * @param row
 * @param dpaProductDO
 * @throws WriteException 
 * @throws RowsExceededException 
 */
private void writeProductLine(int row, DpaProductDO dpaProduct) throws RowsExceededException, WriteException {
	
	writeText(row, 0,dpaProduct.getProduct().getDialogicItemKey() );
	writeText(row, 1,dpaProduct.getProduct().getProductName() );
	writePrice(row, 2,dpaProduct.getSdc() );
	writePrice(row, 3,dpaProduct.getTargetCost());
	writeText(row, 4,dpaProduct.getCompetitor() );
	writePrice(row, 5,dpaProduct.getCompetitorsPrice() );
	writeNumber(row, 6,dpaProduct.getQuantity() );
	writePrice(row, 7,dpaProduct.getApprovedPrice() );
	
}


/**
 * This method reads the excel documents when it returns 
 * it is expected to contain the expiration date . the dpa id in the mazor system
 * and the dpa id
 * 
 * @param file
 * @return
 * @throws BiffException
 * @throws IOException
 */
public DpaExcelData readDPADataFromFile(String file) throws BiffException, IOException{
	Workbook wb = Workbook.getWorkbook( new File(file));    
	Sheet sheet = wb.getSheet(0); 
	
	String dpaId=null;
	int id=0;
	Date expDate = null;
	
	
	Cell cell = sheet.getCell(200, 0);
	try {
		id = Integer.parseInt(cell.getContents());
	}catch (NumberFormatException e) {}
	cell = sheet.getCell(1,7);
	if (!cell.getContents().equals("")){
		dpaId=cell.getContents();
	}
	cell = sheet.getCell(3,7);
	try {
		DateCell  dt = (DateCell ) cell;
		expDate=dt.getDate();
	} catch (Exception e) {}
	
	LinkedList<Double> prices = readAprrovedPrice(sheet);
	return new DpaExcelData(id,dpaId,expDate,prices);
	
}
/**
 * This method reads the approved price of the products from the DPA excel
 * 
 * @param sheet
 * @return a list of all the prices the index are according the the excel
 */
private LinkedList<Double> readAprrovedPrice(Sheet sheet) {
	LinkedList<Double> prices = new LinkedList<Double>();
	try {
		int row=9;
		Cell cell = sheet.getCell(7, row);
		double price=0;
		try {
			price = Double.parseDouble(cell.getContents());
			prices.add(price);
		}catch (NumberFormatException e) {}
		
		while (price !=0){
			row++;
			
			cell = sheet.getCell(7, row);
			try {
				price = Double.parseDouble(cell.getContents());
				prices.add(price);
			}catch (NumberFormatException e) {}
		}
		return prices;
	}catch (ArrayIndexOutOfBoundsException e) {
		return prices;
		}
}

/**
 * This method creates the writes for the cells and sets the column width
 * 
 * @throws WriteException
 */
private void createWritersAndView() throws WriteException {
	WritableFont lablesWriterFont = new WritableFont(WritableFont.ARIAL,9);
	labelCell = new WritableCellFormat(lablesWriterFont);
	labelCell.setBackground(Colour.SKY_BLUE);
	labelCell.setAlignment(Alignment.CENTRE);
	labelCell.setWrap(true);
	labelCell.setBorder(Border.ALL, BorderLineStyle.THIN);
	
	WritableFont dataWriterFont = new WritableFont(WritableFont.ARIAL,9);
	dataCell = new WritableCellFormat(dataWriterFont);
	dataCell.setAlignment(Alignment.CENTRE);
	dataCell.setWrap(true);
	
	dateCell =new WritableCellFormat ( new DateFormat ("dd-mm-yy"));
	dateCell.setAlignment(Alignment.CENTRE);
	dateCell.setWrap(true);

	
	CellView cv = new CellView();
	
	cv.setAutosize(true);
	//special configuration of cols
	excelSheet.setColumnView(0, 18);
	excelSheet.setColumnView(1, 21);
	excelSheet.setColumnView(4, 12);
	excelSheet.setColumnView(5, 11);
	excelSheet.setColumnView(6, 4);
}
/**
 * creates all the cells containing data according to the dpa object
 * 
 * @param dpa
 * @throws RowsExceededException
 * @throws WriteException
 */
private void createContent(DpaDO dpa) throws RowsExceededException, WriteException {
	DateTime reqDate=new DateTime(1, 0, dpa.getRequestDate(), dateCell);
	excelSheet.addCell(reqDate);
	
	writeNumber(0, 200, dpa.getId());
	writeText(1, 1, excelConstats.getProperty("dpa_dis_name"));
	
	
	writeText(1, 3, dpa.getContact());
	
	writeText(2, 1, dpa.getDialogicfse());
	writeText(1, 5, dpa.getEmail());
	
	writeText(3, 1, dpa.getCustomer().getName());
	writeText(3, 3, dpa.getCustomer().getLocation());
	writeText(4, 1, dpa.getEndUserName());
	writeText(5, 1, dpa.getReasonForRequest());
	writeTextOnMergeCells(1, 6,7,6, dpa.getAdditionalInformation());
	
	
	
	
}
/**
 * creates all the labels for the excel sheet
 * 
 * @throws RowsExceededException
 * @throws WriteException
 */
private void createLabel() throws RowsExceededException, WriteException {
	writeLabel(0, 0, excelConstats.getProperty("dpa_date_requested_title"));
	writeLabel(1, 0, excelConstats.getProperty("dpa_dis_name_title"));
	writeLabel(1, 2, excelConstats.getProperty("dpa_contact_title"));
	writeLabel(1, 4, excelConstats.getProperty("dpa_email_title"));
	writeLabel(2, 0, excelConstats.getProperty("dpa_dialogic_contact_title"));
	
	writeLabel(3, 0, excelConstats.getProperty("dpa_cutomer_name_title"));
	writeLabel(3, 2, excelConstats.getProperty("dpa_cutomer_location_title"));
	
	writeLabel(4, 0, excelConstats.getProperty("dpa_end_user_title"));
	
	writeLabel(5, 0, excelConstats.getProperty("dpa_rfr_title"));
	writeLabel(6, 0, excelConstats.getProperty("dpa_add_info_title"));
	writeLabel(7, 0, excelConstats.getProperty("dpa_num_title"));
	writeLabel(7, 2, excelConstats.getProperty("dpa_expires_title"));
	
	writeLabel(8, 0, excelConstats.getProperty("dpa_dialogic_item_title"));
	writeLabel(8, 1, excelConstats.getProperty("dpa_prod_name_title"));
	writeLabel(8, 2, excelConstats.getProperty("dpa_sdc_title"));
	writeLabel(8, 3, excelConstats.getProperty("dpa_trg_cost_title"));
	writeLabel(8, 4, excelConstats.getProperty("dpa_competitor_title"));
	writeLabel(8, 5, excelConstats.getProperty("dpa_competitor_price_title"));
	writeLabel(8, 6, excelConstats.getProperty("dpa_qty_title"));
	writeLabel(8, 7, excelConstats.getProperty("dpa_app_price_title"));
	writeLabel(8, 8, excelConstats.getProperty("dpa_total_title"));
	
}

/**
 * This method writes a text as label (color the cell) into a cell
 * 
 * @param row
 * @param column
 * @param s
 * @throws RowsExceededException
 * @throws WriteException
 */
private void writeLabel(int row, int column, String s) throws RowsExceededException, WriteException {
	Label label;
	label = new Label(column, row, s, labelCell);
	excelSheet.addCell(label);
}
/**
 * This method writes a number into a cell 
 * 
 * @param row
 * @param column
 * @param integer
 * @throws WriteException
 * @throws RowsExceededException
 */
private void writeNumber( int row, int column,Integer integer) throws WriteException, RowsExceededException {
	Number number;
	number = new Number(column, row, integer, dataCell);
	excelSheet.addCell(number);
}
/**
 * This method writes a text  into a cell
 * 
 * @param row
 * @param column
 * @param s
 * @throws RowsExceededException
 * @throws WriteException
 */
private void writeText( int row, int column,String str) throws WriteException, RowsExceededException {
	Label label;
	label = new Label(column, row, str, dataCell);
	
	excelSheet.addCell(label);
}
/**
 * This method writes a text  into a cell while merging cells
 * 
 * @param column
 * @param row
 * @param colTo
 * @param rowTo
 * @param str
 * @throws WriteException
 * @throws RowsExceededException
 */
private void writeTextOnMergeCells( int column, int row,int colTo,int rowTo,String str) throws WriteException, RowsExceededException {
	excelSheet.mergeCells(column, row, colTo, rowTo);
	writeText(rowTo, column, str);
}
/**
 * This method writes a number into a cell 
 * 
 * @param row
 * @param column
 * @param integer
 * @throws WriteException
 * @throws RowsExceededException
 */
private void writePrice(int row, int col, double sdc) throws RowsExceededException, WriteException {
	Number number;
	number = new Number(col, row, sdc, dataCell);
	excelSheet.addCell(number);
	
}


}
